let express = require("express")();
let expressStatic = require("express-static");
let mysql = require("mysql");
let port = 80;

let sql = mysql.createConnection({
    host: "localhost",
    user: "root",
    password: "123456",
    database: "test",
    timezone: "08:00"
})

sql.connect();

// 查询
express.get("/getStudents", (request, response) => {
    sql.query(`select * from students`, (error, data) => {
        if (error) {
            console.log(error);
            response.send("error");
        } else {
            response.send(data);
        }
    })
})

// 删除
express.get("/deleteStudent", (request, response) => {
    if (!request.query.id) {
        return;
    }
    sql.query(`delete from students where id=${request.query.id}`, (error, data) => {
        if (error) {
            console.log(error);
            response.send("error");
        } else {
            response.send("success")
        }
    })
})

// 插入
express.get("/addStudent", (request, response) => {
    let s = request.query;
    sql.query(`insert into students(name,age,sex,city,joinDate) values("${s.name}","${s.age}","${s.sex}","${s.city}","${s.joinDate}")`, (error, data) => {
        if (error) {
            console.log(error);
            response.send("error");
        } else {
            response.send("success")
            console.log(`新增学生${s.name}操作成功`)
        }
    })
})

// 修改
express.get("/editStudent", (request, response) => {
    let s = request.query;
    sql.query(`update students set name="${s.name}",age="${s.age}",sex="${s.sex}", city="${s.city}",joinDate="${s.joinDate}" WHERE id=${s.id}`, (error, data) => {
        if (error) {
            response.send("error");
            console.log(error)
        } else {
            response.send("success")
            console.log(`修改学生${s.name}操作成功`)
            console.log(data)
        }
    })
})

// 登录

express.get("/login", (request, response) => {
    let s = request.query;
    sql.query(`select * from users where username="${s.username}" && password="${s.password}"`, (error, data) => {
        if (error) {
            response.send("1");
            console.log(error);
        }
        if (data.length === 0) {
            response.send("2")
        } else {
            response.send(JSON.stringify(data));

        }
    })
})

// 注册
express.get("/register", (request, response) => {
    let s = request.query;
    sql.query(`insert into users(username,password,name,userType) values("${s.username}","${s.password}","${s.name}",${s.userType})`, (error, data) => {
        if (error) {
            response.send("1");
            console.log(error);
        }
        response.send("success")
        console.log(`${s.username}用户注册成功`)
    })
})



express.use(expressStatic(__dirname + "/static"));
console.log(`running is at ${port}`);
express.listen(port);